﻿Imports System.Data.Linq
Imports System.Linq

Public Class FileRepository
    Private dc As New TanNamChinhDataContext


    Public Sub New()
        dc = GeneralClassDAL.dc
    End Sub

    Public Function SelectAllRecord() As IQueryable(Of tblFile)
        Dim query = From p In dc.tblFiles
                    Select p
        Return query
    End Function

    Public Function SelectRecordById(ByVal id As Integer) As tblFile
        Dim query = dc.tblFiles.SingleOrDefault(Function(p As tblFile) p.ID_File = id)
        Return query
    End Function

    Public Function Insert(ByVal obj As tblFile) As tblFile
        obj.FileNo = dc.HT_NewFileNo(obj.IsExport)
        dc.tblFiles.InsertOnSubmit(obj)
        dc.SubmitChanges()
        Return obj
    End Function

    ''' <summary>
    ''' Copy File chỉ sẽ copy những phần sau:
    ''' + Copy thông tin File
    ''' + Copy thông tin tỉ giá theo File
    ''' + Copy thông tin các Cont của File
    ''' + Copy thông tin MBL của File, bao gồm copy:
    '''    - Copy thông tin Voyage
    '''    - Copy thông tin giá cước: bao gồm copy các giá cước FreightTariff và VoyageFee
    ''' + Copy HBL nếu chỉ có 1 HBL, ngoài ra
    '''    - Copy tỉ giá theo HBL
    ''' </summary>
    ''' <param name="objSrcFile"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InsertByCopyFile(ByVal objSrcFile As tblFile) As tblFile
        Dim objDesFile As tblFile = HTLibrary.CollectionHelper.ShallowClone(objSrcFile)
        objDesFile.ID_File = Nothing
        objDesFile.FileNo = dc.HT_NewFileNo(objDesFile.IsExport)
        dc.tblFiles.InsertOnSubmit(objDesFile)

        '===============================================================================================
        'Update 2014-04-25:
        'Copy FileExchange
        For Each objScrFileExchange As tblFileExchange In objSrcFile.tblFileExchanges
            'Copy FileExchange
            Dim objDesFileExchange As tblFileExchange = HTLibrary.CollectionHelper.ShallowClone(objScrFileExchange)
            objDesFileExchange.ID_FileExchange = Nothing
            'Add objDesCont vào list FileExchange của objDesFile
            objDesFile.tblFileExchanges.Add(objDesFileExchange)
            'Insert vào tblFileExchange
            dc.tblFileExchanges.InsertOnSubmit(objDesFileExchange)
        Next
        dc.SubmitChanges()

        '===============================================================================================
        'Copy Cont
        '* Không copy ContFee vì thông tin vận chuyển sẽ khác nhau
        For Each objSrcCont As tblCont In objSrcFile.tblConts
            'Copy Cont
            Dim objDesCont As tblCont = HTLibrary.CollectionHelper.ShallowClone(objSrcCont)
            objDesCont.ID_Cont = Nothing
            objDesCont.ContNo = Nothing
            objDesCont.ID_HBL = Nothing
            'Add objDesCont vào list Cont của objDesFile (objDesCont.ID_File = objDesFile.ID_File)
            objDesFile.tblConts.Add(objDesCont)

            '** Không copy ContFee vì thông tin vận chuyển sẽ khác nhau
            'Copy ContFee of Cont
            'For Each objSrcContFee In objSrcCont.tblContFees
            '    Dim objDesContFee As tblContFee = HTLibrary.CollectionHelper.ShallowClone(objSrcContFee)
            '    objDesContFee.ID_ContFee = Nothing
            'Next

            'Insert vào tblCont
            dc.tblConts.InsertOnSubmit(objDesCont)
        Next
        dc.SubmitChanges()

        '===============================================================================================
        'Copy tblFileMBL
        For Each objSrcFileMBL As tblFileMBL In objSrcFile.tblFileMBLs
            'Copy MBL, Voyage, VoyageFee
            If objSrcFileMBL.tblMBL IsNot Nothing Then
                Dim objDesMBL As tblMBL = HTLibrary.CollectionHelper.ShallowClone(objSrcFileMBL.tblMBL)
                '===============================================================================================
                'Copy Voyage, VoyageFee
                If objSrcFileMBL.tblMBL.tblVoyage IsNot Nothing Then
                    Dim objDesVoyage As tblVoyage = HTLibrary.CollectionHelper.ShallowClone(objSrcFileMBL.tblMBL.tblVoyage)
                    objDesVoyage.ID_Voyage = Nothing
                    '=========================
                    'Copy VoyageFee
                    For Each objSrcVoyageFee As tblVoyageFee In objSrcFileMBL.tblMBL.tblVoyage.tblVoyageFees
                        Dim objDesVoyageFee As tblVoyageFee = HTLibrary.CollectionHelper.ShallowClone(objSrcVoyageFee)
                        objDesVoyageFee.ID_Voyage = Nothing

                        'Gán objDesVoyageFee.ID_Voyage = objDesVoyage.ID_Voyage
                        objDesVoyage.tblVoyageFees.Add(objDesVoyageFee)
                    Next

                    '=========================
                    'Copy FreightTariffs
                    For Each objSrcFreightTariff As tblFreightTariff In objSrcFileMBL.tblMBL.tblVoyage.tblFreightTariffs
                        Dim objDesFreightTariff As tblFreightTariff = HTLibrary.CollectionHelper.ShallowClone(objSrcFreightTariff)
                        objDesFreightTariff.ID_Voyage = Nothing

                        'Gán objDesFreightTariff.ID_Voyage = objDesVoyage.ID_Voyage
                        objDesVoyage.tblFreightTariffs.Add(objDesFreightTariff)
                    Next
                    dc.tblVoyages.InsertOnSubmit(objDesVoyage)
                    dc.SubmitChanges()

                    'Update ID_Voyage in tblMBL
                    objDesMBL.ID_Voyage = objDesVoyage.ID_Voyage
                End If

                '===============================================================================================
                'Copy MBL
                objDesMBL.ID_MBL = Nothing
                objDesMBL.MBLNo = Nothing
                dc.tblMBLs.InsertOnSubmit(objDesMBL)
                dc.SubmitChanges()

                '===============================================================================================
                'Copy FileMBL
                Dim objDesFileMBL As tblFileMBL = HTLibrary.CollectionHelper.ShallowClone(objSrcFileMBL)
                objDesFileMBL.ID_File = objDesFile.ID_File
                objDesFileMBL.ID_FileMBL = Nothing

                '===============================================================================================
                'Nếu source File chỉ có 1 HBL duy nhất thì mới copy, nếu có nhiều hơn 1 thì không copy
                'Khi copy thì mặc định chuyển sang MasterBill vì HBLNo không có
                If objSrcFileMBL.tblHBLs.Count = 1 Then
                    For Each objSrcHBL As tblHBL In objSrcFileMBL.tblHBLs
                        Dim objDesHBL As tblHBL = HTLibrary.CollectionHelper.ShallowClone(objSrcHBL)
                        objDesHBL.ID_HBL = Nothing
                        'Chuyển HBL về MasterBill (để không bị trùng HBLNo)
                        objDesHBL.HBLNo = "MASTER"
                        objDesHBL.IsMaster = True
                        objDesHBL.ID_TaxReturn = Nothing

                        '===============================================================================================
                        'Copy SellingFee của HBL
                        For Each objSrcSellingFee As tblSellingFee In objSrcHBL.tblSellingFees
                            Dim objDesSellingFee = HTLibrary.CollectionHelper.ShallowClone(objSrcSellingFee)
                            objDesSellingFee.ID_SellingFee = Nothing
                            objDesHBL.tblSellingFees.Add(objDesSellingFee)
                        Next

                        '===============================================================================================
                        'Ngày 2014-04-25:
                        'Copy HBLExchange của HBL
                        For Each objScrHBLExchange As tblHBLExchange In objSrcHBL.tblHBLExchanges
                            Dim objDesHBLExchange = HTLibrary.CollectionHelper.ShallowClone(objScrHBLExchange)
                            objDesHBLExchange.ID_HBLExchange = Nothing
                            objDesHBL.tblHBLExchanges.Add(objDesHBLExchange)
                        Next

                        objDesFileMBL.tblHBLs.Add(objDesHBL)
                    Next
                End If

                'Update ID_MBL in tblFileMBL
                objDesFileMBL.ID_MBL = objDesMBL.ID_MBL
                dc.tblFileMBLs.InsertOnSubmit(objDesFileMBL)
                dc.SubmitChanges()

                

            End If
        Next

        '===============================================================================================
        'Copy FileFee
        For Each objSrcFileFee As tblFileFee In objSrcFile.tblFileFees
            Dim objDesFileFee As tblFileFee = HTLibrary.CollectionHelper.ShallowClone(objSrcFileFee)
            objDesFileFee.ID_FileFee = Nothing
            objDesFileFee.ID_File = objDesFile.ID_File
            dc.tblFileFees.InsertOnSubmit(objDesFileFee)
        Next
        dc.SubmitChanges()

        Return objDesFile
    End Function

    'Public Function InsertByCopyFile(ByVal obj As tblFile) As tblFile
    '    Dim dc2 As New TanNamChinhDataContext
    '    dc2.ObjectTrackingEnabled = False
    '    Dim objNewFile As tblFile = dc2.tblFiles.SingleOrDefault(Function(p As tblFile) p.ID_File = obj.ID_File)
    '    objNewFile.ID_File = Nothing
    '    objNewFile.FileNo = dc.HT_NewFileNo(obj.IsExport)

    '    'Copy Cont
    '    For Each cont As tblCont In objNewFile.tblConts
    '        cont.ID_Cont = Nothing
    '    Next
    '    dc.tblFiles.InsertOnSubmit(obj)

    '    'Copy tblFileMBL
    '    objNewFile.tblFileMBLs(0).ID_FileMBL = Nothing
    '    dc.tblFiles.InsertOnSubmit(obj)

    '    'Copy tblHBL
    '    For Each hbl As tblHBL In objNewFile.tblFileMBLs(0).tblHBLs
    '        hbl.ID_HBL = Nothing
    '        hbl.IsMaster = True
    '        hbl.HBLNo = "MASTER"
    '    Next
    '    dc.tblFiles.InsertOnSubmit(obj)

    '    'Copy tblMBL
    '    objNewFile.tblFileMBLs(0).tblMBL.ID_MBL = Nothing
    '    dc.tblFiles.InsertOnSubmit(obj)

    '    objNewFile.tblFileMBLs(0).tblMBL.tblVoyage.ID_Voyage = Nothing
    '    dc.tblFiles.InsertOnSubmit(obj)
    '    dc.SubmitChanges()

    '    'Copy File

    '    'Copy MBL
    '    Return obj
    'End Function

    Public Sub Update(ByVal obj As tblFile)
        dc.Refresh(RefreshMode.KeepChanges, obj)
        dc.SubmitChanges(ConflictMode.ContinueOnConflict)
    End Sub

    ''' <summary>
    ''' Kiểm tra tồn tại FileNo
    ''' </summary>
    ''' <param name="isExport"></param>
    ''' <param name="fileNo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsExistFileNo(ByVal isExport As Boolean, ByVal fileNo As String) As Boolean
        Dim count = (From p In dc.tblFiles
                    Where p.FileNo = fileNo And p.IsExport = isExport
                    Select p).Count
        If count > 0 Then
            Return True
        End If
        Return False
    End Function

    ''' <summary>
    ''' Xóa File và các cascade liên quan
    ''' Các cascade khi xóa một File ở tblFile:
    '''  + tblFileFee
    '''  + tblFileMBL: xóa FileMBl sẽ cascade các bảng sau:
    '''      - tblHBL: xóa HBL sẽ cascade các bảng sau:
    '''            * tblSellingFee (Cascade)
    '''            * tblComercialInvoice (Cascade)
    '''            ** Trong CSDL: Không cascade tblCont (Not action) vì đã xóa từ File rồi mà nếu cascade hoặc set null thì sẽ bị báo lỗi
    '''  + tblCont: xóa Cont sẽ cascade các bảng sau:
    '''      - tblContFee
    '''  + tblFileExchange
    ''' </summary>
    ''' <param name="obj"></param>
    ''' <remarks></remarks>
    Private Sub Delete_FileAndCascade(ByVal obj As tblFile)
        dc.tblFiles.DeleteOnSubmit(obj)
        dc.SubmitChanges()
    End Sub

    ''' <summary>
    ''' Xóa Voyage không dùng. Voyage không dùng khi:
    '''  + Không nằm trong bảng tblMBL 
    '''  + Hoặc nằm trong tblMBL nhưng MBL lại không nằm trong tblFileMBL
    ''' Khi xóa Voyage sẽ cascade các bảng sau:
    '''  + tblVoyageFee
    '''  + tblFreighTariff
    '''  + tblMBL
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub Delete_UnusedVoyageAndCascade()
        'Xóa Voyage không nằm trong bảng tblMBL
        Dim query = From p In dc.tblVoyages
                    Where p.tblMBLs.Count = 0
                    Select p
        For Each obj As tblVoyage In query
            dc.tblVoyages.DeleteOnSubmit(query)
        Next
        dc.SubmitChanges()

        'Xóa Voyage nằm trong tblMBL nhưng MBL lại không nằm trong tblFileMBL
        query = (From p In dc.tblMBLs
                    Where p.tblFileMBLs.Count = 0
                    Select p.tblVoyage).Distinct()
        For Each obj As tblVoyage In query
            dc.tblVoyages.DeleteOnSubmit(obj)
        Next
        dc.SubmitChanges()
    End Sub

    ''' <summary>
    ''' Xóa AdvanceVoucher không dùng. AdvanceVoucher không dùng khi:
    '''    + Không tồn tại Fee nào
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub Delete_UnusedAdvanceVoucherAndCascade()
        Dim query = From p In dc.tblAdvanceVouchers
                    Where p.tblFileFees.Count = 0
                    Select p
        For Each obj As tblAdvanceVoucher In query
            dc.tblAdvanceVouchers.DeleteOnSubmit(obj)
        Next
        dc.SubmitChanges()
    End Sub

    ''' <summary>
    ''' Xóa ReceiptVoucher không dùng. ReceiptVoucher không dùng khi:
    '''    + Không tồn tại SellingFee nào và không có nằm trong AdvanceVoucher ( ==> xem lại thiết kế CSDL hiện tại hơi ngược giữa tblAdvanceVoucher và tblReceiptVoucher)
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub Delete_UnusedReceiptVoucherAndCascade()
        'Xóa ReceiptVoucher không tồn tại SellingFee và không nằm trong tblAdvanceVoucher
        Dim query = From p In dc.tblReceiptVouchers
                    Where p.tblSellingFees.Count = 0 And p.tblAdvanceVouchers.Count = 0
                    Select p
        For Each obj As tblReceiptVoucher In query
            dc.tblReceiptVouchers.DeleteOnSubmit(obj)
        Next
        dc.SubmitChanges()

        'Xóa ReceiptVoucher không tồn tại SellingFee và không nằm trong tblAdvanceVoucher
        'query = From p In dc.tblAdvanceVouchers
        '        Where p.tblSellingFees.Count = 0
        '        Select p
        'For Each obj As tblReceiptVoucher In query
        '    dc.tblReceiptVouchers.DeleteOnSubmit(obj)
        'Next
    End Sub

    ''' <summary>
    ''' Xóa PaymentVoucher không dùng. PaymentVoucher không dùng khi:
    '''    + Không tồn tại FileFee (BuyingFee) nào
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub Delete_UnusedPaymentVoucherAndCascade()
        'Xóa PaymentVoucher không tồn tại FileFee (BuyingFee) nào
        Dim query = From p In dc.tblPaymentVouchers
                    Where p.tblFileFees.Count = 0
                    Select p
        For Each obj As tblPaymentVoucher In query
            dc.tblPaymentVouchers.DeleteOnSubmit(obj)
        Next
        dc.SubmitChanges()
    End Sub

    ''' <summary>
    ''' Xử lý xóa File
    ''' </summary>
    ''' <param name="obj"></param>
    ''' <remarks></remarks>
    Public Sub Delete(ByVal obj As tblFile)
        '1. Xóa file và cascade liên quan
        Delete_FileAndCascade(obj)
        '2. Xóa Voyage không dùng và cascade liên quan
        Delete_UnusedVoyageAndCascade()
        '3. Xóa Advance Voucher không dùng và cascade liên quan
        Delete_UnusedAdvanceVoucherAndCascade()
        '4. Xóa Receipt Voucher không dùng và cascade liên quan
        Delete_UnusedReceiptVoucherAndCascade()
        '5. Xóa Payment Voucher không dùng và cascade liên quan
        Delete_UnusedPaymentVoucherAndCascade()
    End Sub

    ''' <summary>
    ''' Hủy File
    ''' </summary>
    ''' <param name="obj"></param>
    ''' <remarks></remarks>
    Public Sub Cancel(ByVal obj As tblFile, ByVal isCancel As Boolean)
        obj.Status = isCancel
        dc.SubmitChanges()
    End Sub

    'Public Sub Delete(ByVal obj As tblFile)
    '    Dim result
    '    'Kiểm tra xem có tồn tại FileMBL không?
    '    If obj.tblFileMBLs.Count > 0 Then
    '        'Kiểm tra nếu tồn tại phí BuyingFee thì xóa AdvanveVoucher, PaymentVoucher

    '        'Xóa các AdvanceVoucher nếu có
    '        result = (From p In dc.tblFileFees
    '                 Where p.ID_File = obj.ID_File
    '                 Select p.tblAdvanceVoucher).Distinct()
    '        For Each objAdvanceVoucher As tblAdvanceVoucher In result
    '            If objAdvanceVoucher IsNot Nothing Then
    '                dc.tblAdvanceVouchers.DeleteOnSubmit(objAdvanceVoucher)
    '            End If
    '        Next

    '        'Xóa các PaymentVoucher nếu có
    '        result = (From p In dc.tblFileFees
    '                 Where p.ID_File = obj.ID_File
    '                 Select p.tblPaymentVoucher).Distinct()
    '        For Each objPaymentVoucher As tblPaymentVoucher In result
    '            If objPaymentVoucher IsNot Nothing Then
    '                dc.tblPaymentVouchers.DeleteOnSubmit(objPaymentVoucher)
    '            End If
    '        Next

    '        'Kiểm tra nếu tồn tại HBL
    '        If obj.tblFileMBLs(0).tblHBLs.Count() > 0 Then
    '            'Kiểm tra nếu tồn tại SellingFee
    '            For Each objHBL As tblHBL In obj.tblFileMBLs(0).tblHBLs
    '                'Xóa các PaymentVoucher liên quan đến SellingFee của HBL nếu có
    '                result = (From p In dc.tblSellingFees
    '                         Where p.ID_HBL = objHBL.ID_HBL
    '                         Select p.tblReceiptVoucher).Distinct()
    '                For Each objReceiptVoucher As tblReceiptVoucher In result
    '                    If objReceiptVoucher IsNot Nothing Then
    '                        dc.tblReceiptVouchers.DeleteOnSubmit(objReceiptVoucher)
    '                    End If
    '                Next
    '            Next

    '            'Xóa các HBL và các SellingFee liên quan
    '            dc.tblHBLs.DeleteAllOnSubmit(obj.tblFileMBLs(0).tblHBLs)
    '        End If

    '        'Kiểm tra nếu MBL chỉ dùng cho mỗi File này thì xóa
    '        If obj.tblFileMBLs(0).tblMBL.tblFileMBLs.Count() = 1 Then
    '            'Xóa Voyage, xóa MBL và các VoyageFee liên quan
    '            dc.tblVoyages.DeleteOnSubmit(obj.tblFileMBLs(0).tblMBL.tblVoyage)
    '        End If
    '    End If

    '    'Xóa File, FileFee, FileMBL, Cont và ContFee
    '    dc.tblFiles.DeleteOnSubmit(obj)
    '    dc.SubmitChanges()
    'End Sub

    Public Function SelectAllRecord(ByVal isExport As Boolean) As IQueryable(Of tblFile)
        Dim query = From p In dc.tblFiles
                    Where p.IsExport = isExport And p.FileNo IsNot Nothing
                    Select p
        Return query
    End Function

    'Public Function SelectCustomAllRecord(ByVal isExport As Boolean) As IQueryable
    '    Dim query = From p In dc.tblFiles
    '                Where p.IsExport = isExport And p.FileNo IsNot Nothing
    '                Select New With {
    '                    p.ID_File, p.tblUser.UserName, p.FileNo, p.tblCustomer.CustomerName, p.tblCompany.CompanyName, p.tblForeignCompany.Name, p.tblAgent.AgentName}
    '    Return query
    'End Function

    'Public Function SelectCustomAllRecord(ByVal isExport As Boolean, Optional ByVal strCondition As String = "") As IQueryable
    '    Dim query = dc.ExecuteQuery(Of IQueryable)("select * from ViewHT_Files where FileNo is not null and IsExport = {0} {1}", isExport, strCondition)
    '    Return query
    'End Function

    ''' <summary>
    ''' Select all records
    ''' </summary>
    ''' <param name="isExport"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SelectCustomAllRecord(ByVal isExport As Boolean) As System.Linq.IQueryable
        Dim query = (From p In dc.tblFiles
                    Where p.IsExport = isExport And p.FileNo IsNot Nothing
                     Select
                        p.ID_File, p.tblUser.UserName, p.FileNo, p.tblCustomer.CustomerName, _
                        Company = p.tblCompany.Name, ForeignCompany = p.tblForeignCompany.Name, Agent = p.tblAgent.Name, _
                        p.IsFCL, p.IsD2D, p.LineBookingNo).OrderByDescending(Function(p) p.FileNo)

        Return query
    End Function

    ''' <summary>
    ''' Select records with conditions
    ''' </summary>
    ''' <param name="isExport"></param>
    ''' <param name="strCustomer"></param>
    ''' <param name="strCompany"></param>
    ''' <param name="strForeignCompany"></param>
    ''' <param name="D2DValue"></param>
    ''' <param name="FCLValue"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SelectCustomAllRecord(ByVal isExport As Boolean, _
                                          ByVal D2DValue As Integer, _
                                          ByVal FCLValue As Integer, _
                                          ByVal strFileNo As String, _
                                          ByVal strCustomer As String, _
                                          ByVal strCompany As String, _
                                          ByVal strForeignCompany As String, _
                                          ByVal strAgent As String, _
                                          ByVal strNoiHaCont As String, _
                                          ByVal strNoiNangCont As String, _
                                          ByVal strTerm As String, _
                                          ByVal strOtherCurrency As String, _
                                          ByVal strLineBookingNo As String, _
                                          ByVal strMBLNo As String, _
                                          ByVal strLine As String, _
                                          ByVal strVessel As String, _
                                          ByVal strVoyageNo As String, _
                                          ByVal strPOL As String, _
                                          ByVal strPOD As String, _
                                          ByVal strTransPort As String, _
                                          ByVal dtETDFrom As DateTime, _
                                          ByVal dtETDTo As DateTime, _
                                          ByVal dtETAFrom As DateTime, _
                                          ByVal dtETATo As DateTime, _
                                          ByVal isMaster As Integer, _
                                          ByVal isCareOf As Integer, _
                                          ByVal strHBLNo As String, _
                                          ByVal strConditionCBM_HBL As String, _
                                          ByVal valueCBM_HBL As Double, _
                                          ByVal strConditionNW_HBL As String, _
                                          ByVal valueNW_HBL As Double, _
                                          ByVal strConditionGW_HBL As String, _
                                          ByVal valueGW_HBL As Double, _
                                          ByVal strNotifyParty As String, _
                                          ByVal strMarksAndNumbers As String, _
                                          ByVal strGoodDescription As String, _
                                          ByVal strContNo As String, _
                                          ByVal strContType As String, _
                                          ByVal strSeal As String, _
                                          ByVal strTypeOfGood As String, _
                                          ByVal strPackage As String, _
                                          ByVal strConditionCBM_Cont As String, _
                                          ByVal valueCBM_Cont As Double, _
                                          ByVal strConditionNW_Cont As String, _
                                          ByVal valueNW_Cont As Double, _
                                          ByVal strConditionGW_Cont As String, _
                                          ByVal valueGW_Cont As Double, _
                                          ByVal strContNote As String) As IQueryable

        Dim query = From pFile In dc.tblFiles
                    Group Join q In dc.tblFileMBLs
                    On pFile.ID_File Equals q.ID_File Into FileMBLRightTableResults = Group
                    From pFileMBL In FileMBLRightTableResults.DefaultIfEmpty
                    Group Join c In dc.tblConts
                    On pFile.ID_File Equals c.ID_File Into ContRightTableResults = Group
                    From pCont In ContRightTableResults.DefaultIfEmpty
                    Where pFile.IsExport = isExport And pFile.FileNo IsNot Nothing
                    Group Join h In dc.tblHBLs
                    On pFileMBL.ID_FileMBL Equals h.ID_FileMBL Into HBLRightTableResults = Group
                    From pHBL In HBLRightTableResults.DefaultIfEmpty
                    Select pFile, pFileMBL, pHBL, pCont


        'File Search

        If FCLValue = 1 Then
            query = query.Where(Function(p) p.pFile.IsFCL = True)
        ElseIf FCLValue = 0 Then
            query = query.Where(Function(p) p.pFile.IsFCL = False)
        End If

        If D2DValue = 1 Then
            query = query.Where(Function(p) p.pFile.IsD2D = True)
        ElseIf D2DValue = 0 Then
            query = query.Where(Function(p) p.pFile.IsD2D = False)
        End If

        If strFileNo <> "" Then
            query = query.Where(Function(p) p.pFile.FileNo = strFileNo)
        End If

        If strCustomer <> "" Then
            query = query.Where(Function(p) p.pFile.tblCustomer.CustomerName.Equals(strCustomer))
        End If

        If strCompany <> "" Then
            query = query.Where(Function(p) p.pFile.tblCompany.Name.Equals(strCompany))
        End If

        If strForeignCompany <> "" Then
            query = query.Where(Function(p) p.pFile.tblForeignCompany.Name.Equals(strForeignCompany))
        End If

        If strAgent <> "" Then
            query = query.Where(Function(p) p.pFile.tblAgent.Name.Equals(strAgent))
        End If

        If strNoiHaCont <> "" Then
            query = query.Where(Function(p) p.pFile.tblPort.Port.ToString.Equals(strNoiHaCont))
        End If

        If strNoiNangCont <> "" Then
            query = query.Where(Function(p) p.pFile.tblPort1.Port.ToString.Equals(strNoiNangCont))
        End If

        '==========================================
        'MBL Search
        If strLineBookingNo <> "" Then
            query = From z In query.Where(Function(p) p.pFile.LineBookingNo.Equals(strLineBookingNo))
        End If

        If strMBLNo <> "" Then
            query = query.Where(Function(p) p.pFileMBL.tblMBL.MBLNo.Equals(strMBLNo))
        End If

        If strLine <> "" Then
            query = query.Where(Function(p) p.pFileMBL.tblMBL.tblVoyage.tblLine.LineName.Equals(strLine))
        End If

        If strVessel <> "" Then
            query = query.Where(Function(p) p.pFileMBL.tblMBL.tblVoyage.tblVessel.VesselName.Equals(strVessel))
        End If

        If strVoyageNo <> "" Then
            query = query.Where(Function(p) p.pFileMBL.tblMBL.tblVoyage.VoyageNo.Equals(strVoyageNo))
        End If

        If strPOL <> "" Then
            query = query.Where(Function(p) p.pFileMBL.tblMBL.tblVoyage.tblPort.Port.Equals(strPOL))
        End If

        If strPOD <> "" Then
            query = query.Where(Function(p) p.pFileMBL.tblMBL.tblVoyage.tblPort1.Port.Equals(strPOD))
        End If

        If strTransPort <> "" Then
            query = query.Where(Function(p) p.pFileMBL.tblMBL.tblVoyage.tblPort2.Port.Equals(strTransPort))
        End If

        If dtETDFrom.Date <> "#12:00:00 AM#" Then
            query = query.Where(Function(p) p.pFileMBL.tblMBL.tblVoyage.ETD.Value >= dtETDFrom.Date)
        End If
        If dtETDTo.Date <> "#12:00:00 AM#" Then
            query = query.Where(Function(p) p.pFileMBL.tblMBL.tblVoyage.ETD.Value <= dtETDTo.Date)
        End If

        If dtETAFrom.Date <> "#12:00:00 AM#" Then
            query = query.Where(Function(p) p.pFileMBL.tblMBL.tblVoyage.ETA.Value >= dtETAFrom.Date)
        End If
        If dtETATo.Date <> "#12:00:00 AM#" Then
            query = query.Where(Function(p) p.pFileMBL.tblMBL.tblVoyage.ETA.Value <= dtETATo.Date)
        End If

        '==========================================
        'HBL Search
        If strHBLNo <> "" Then
            query = query.Where(Function(p) p.pHBL.HBLNo.Equals(strHBLNo))
        End If

        If isMaster >= 0 Then
            query = query.Where(Function(p) p.pHBL.IsMaster = isMaster)
        End If

        If isCareOf >= 0 Then
            query = query.Where(Function(p) p.pHBL.IsCareOf = isCareOf)
        End If

        Select Case strConditionCBM_HBL
            Case ">"
                query = query.Where(Function(p) p.pHBL.CBM > valueCBM_HBL)
            Case ">="
                query = query.Where(Function(p) p.pHBL.CBM >= valueCBM_HBL)
            Case "="
                query = query.Where(Function(p) p.pHBL.CBM = valueCBM_HBL)
            Case "<="
                query = query.Where(Function(p) p.pHBL.CBM <= valueCBM_HBL)
            Case "<"
                query = query.Where(Function(p) p.pHBL.CBM < valueCBM_HBL)
        End Select

        Select Case strConditionNW_HBL
            Case ">"
                query = query.Where(Function(p) p.pHBL.NW > valueNW_HBL)
            Case ">="
                query = query.Where(Function(p) p.pHBL.NW >= valueNW_HBL)
            Case "="
                query = query.Where(Function(p) p.pHBL.NW = valueNW_HBL)
            Case "<="
                query = query.Where(Function(p) p.pHBL.NW <= valueNW_HBL)
            Case "<"
                query = query.Where(Function(p) p.pHBL.NW < valueNW_HBL)
        End Select

        Select Case strConditionGW_HBL
            Case ">"
                query = query.Where(Function(p) p.pHBL.GW > valueGW_HBL)
            Case ">="
                query = query.Where(Function(p) p.pHBL.GW >= valueGW_HBL)
            Case "="
                query = query.Where(Function(p) p.pHBL.GW = valueGW_HBL)
            Case "<="
                query = query.Where(Function(p) p.pHBL.GW <= valueGW_HBL)
            Case "<"
                query = query.Where(Function(p) p.pHBL.GW < valueGW_HBL)
        End Select

        If strNotifyParty <> "" Then
            query = query.Where(Function(p) p.pHBL.NotifyParty.Equals(strNotifyParty))
        End If

        If strMarksAndNumbers <> "" Then
            query = query.Where(Function(p) p.pHBL.MarkNumber.Equals(strMarksAndNumbers))
        End If

        If strGoodDescription <> "" Then
            query = query.Where(Function(p) p.pHBL.GoodDescription.Equals(strGoodDescription))
        End If

        '==========================================
        'Container Search
        If strContNo <> "" Then
            query = query.Where(Function(p) p.pCont.ContNo.Equals(strContNo))
        End If

        If strContType <> "" Then
            query = query.Where(Function(p) p.pCont.tblContType.ContTypeName.Equals(strContType))
        End If

        If strSeal <> "" Then
            query = query.Where(Function(p) p.pCont.Seal.Equals(strSeal))
        End If

        If strGoodDescription <> "" Then
            query = query.Where(Function(p) p.pCont.tblGood.GoodName.Equals(strGoodDescription))
        End If

        If strPackage <> "" Then
            query = query.Where(Function(p) p.pCont.tblUnit.UnitName.Equals(strPackage))
        End If

        If strGoodDescription <> "" Then
            query = query.Where(Function(p) p.pCont.tblGood.GoodName.Equals(strGoodDescription))
        End If

        Select Case strConditionCBM_Cont
            Case ">"
                query = query.Where(Function(p) p.pCont.CBM > valueCBM_Cont)
            Case ">="
                query = query.Where(Function(p) p.pCont.CBM >= valueCBM_Cont)
            Case "="
                query = query.Where(Function(p) p.pCont.CBM = valueCBM_Cont)
            Case "<="
                query = query.Where(Function(p) p.pCont.CBM <= valueCBM_Cont)
            Case "<"
                query = query.Where(Function(p) p.pCont.CBM < valueCBM_Cont)
        End Select

        Select Case strConditionNW_Cont
            Case ">"
                query = query.Where(Function(p) p.pCont.NW > valueNW_Cont)
            Case ">="
                query = query.Where(Function(p) p.pCont.NW >= valueNW_Cont)
            Case "="
                query = query.Where(Function(p) p.pCont.NW = valueNW_Cont)
            Case "<="
                query = query.Where(Function(p) p.pCont.NW <= valueNW_Cont)
            Case "<"
                query = query.Where(Function(p) p.pCont.NW < valueNW_Cont)
        End Select

        Select Case strConditionGW_Cont
            Case ">"
                query = query.Where(Function(p) p.pCont.GW > valueGW_Cont)
            Case ">="
                query = query.Where(Function(p) p.pCont.GW >= valueGW_Cont)
            Case "="
                query = query.Where(Function(p) p.pCont.GW = valueGW_Cont)
            Case "<="
                query = query.Where(Function(p) p.pCont.GW <= valueGW_Cont)
            Case "<"
                query = query.Where(Function(p) p.pCont.GW < valueGW_Cont)
        End Select

        'Dim result As IQueryable = (From p In query
        '                            Select p.pFile.ID_File, p.pFile.tblUser.UserName, p.pFile.FileNo, p.pFile.tblCustomer.CustomerName, _
        '                                Company = p.pFile.tblCompany.Name, ForeignCompany = p.pFile.tblForeignCompany.Name, Agent = p.pFile.tblAgent.Name, _
        '                                p.pFile.IsFCL, p.pFile.IsD2D, p.pFile.LineBookingNo, NoiHaCont = p.pFile.tblPort.Port, NoiNangCont = p.pFile.tblPort1.Port, _
        '                                p.pFileMBL.tblMBL.MBLNo, p.pFileMBL.tblMBL.tblVoyage.tblLine.LineName, p.pFileMBL.tblMBL.tblVoyage.tblVessel.VesselName, _
        '                                p.pFileMBL.tblMBL.tblVoyage.VoyageNo, POL = p.pFileMBL.tblMBL.tblVoyage.tblPort.Port, POD = p.pFileMBL.tblMBL.tblVoyage.tblPort1.Port, _
        '                                TransPort = p.pFileMBL.tblMBL.tblVoyage.tblPort2.Port, p.pFileMBL.tblMBL.tblVoyage.ETD, p.pFileMBL.tblMBL.tblVoyage.ETA).OrderByDescending(Function(p) p.FileNo).Distinct()

        Dim result As IQueryable = From p In query
                                    Select p.pFile.ID_File, p.pFile.tblUser.UserName, p.pFile.FileNo, p.pFile.tblCustomer.CustomerName, _
                                        Company = p.pFile.tblCompany.Name, ForeignCompany = p.pFile.tblForeignCompany.Name, Agent = p.pFile.tblAgent.Name, _
                                        p.pFile.IsFCL, p.pFile.IsD2D, p.pFile.LineBookingNo, NoiHaCont = p.pFile.tblPort.Port, NoiNangCont = p.pFile.tblPort1.Port, _
                                        p.pFileMBL.tblMBL.MBLNo, p.pFileMBL.tblMBL.tblVoyage.tblLine.LineName, p.pFileMBL.tblMBL.tblVoyage.tblVessel.VesselName, _
                                        p.pFileMBL.tblMBL.tblVoyage.VoyageNo, POL = p.pFileMBL.tblMBL.tblVoyage.tblPort.Port, POD = p.pFileMBL.tblMBL.tblVoyage.tblPort1.Port, _
                                        TransPort = p.pFileMBL.tblMBL.tblVoyage.tblPort2.Port, p.pFileMBL.tblMBL.tblVoyage.ETD, p.pFileMBL.tblMBL.tblVoyage.ETA, p.pFile.Status, p.pFile.OrderNum, p.pFile.OrderAlpha
                                       Distinct Order By OrderNum Descending, OrderAlpha Descending

        Return result

    End Function


    ''' <summary>
    ''' Lấy File No mới nhất
    ''' </summary>
    ''' <param name="isExport"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNewOrderNum(ByVal isExport As Boolean) As Integer
        Dim orderNum As Integer = dc.HT_NewFileNo(isExport)
        Return orderNum
    End Function
End Class
